package org.example;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;

public class LibrarySystem {
    private static final String URL = "jdbc:mysql://localhost:3306/";
    private static final String DB_NAME = "library_db";
    private static final String USER = "root";
    private static final String PASSWORD = "zzzz4242";
    private static final String FULL_URL = URL + DB_NAME + "?useSSL=false&serverTimezone=UTC";

    public static void main(String[] args) {
        try {
            createDatabase();
            createTables();
            Scanner scanner = new Scanner(System.in);

            while (true) {
                System.out.println("\n请选择操作：");
                System.out.println("1. 查询");
                System.out.println("2. 插入");
                System.out.println("3. 删除");
                System.out.println("4. 退出");
                int choice = scanner.nextInt();
                scanner.nextLine();

                switch (choice) {
                    case 1:
                        handleQuery(scanner);
                        break;
                    case 2:
                        handleInsert(scanner);
                        break;
                    case 3:
                        handleDelete(scanner);
                        break;
                    case 4:
                        System.out.println("程序已退出。");
                        return;
                    default:
                        System.out.println("无效选择，请重新输入。");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createDatabase() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {
            String sql = "CREATE DATABASE IF NOT EXISTS " + DB_NAME;
            stmt.executeUpdate(sql);
            System.out.println("数据库 " + DB_NAME + " 创建成功");
        }
    }

    private static void createTables() throws SQLException {
        try (Connection conn = DriverManager.getConnection(FULL_URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            // 创建管理人员表
            String createStaffTable = "CREATE TABLE IF NOT EXISTS staff (" +
                    "card_id VARCHAR(20) PRIMARY KEY," +
                    "name VARCHAR(50) NOT NULL," +
                    "gender ENUM('男', '女') NOT NULL," +
                    "age INT NOT NULL," +
                    "id_number VARCHAR(18) NOT NULL UNIQUE," +
                    "create_time DATETIME NOT NULL" +
                    ")";
            stmt.executeUpdate(createStaffTable);

            // 创建图书表
            String createBookTable = "CREATE TABLE IF NOT EXISTS books (" +
                    "book_id VARCHAR(20) PRIMARY KEY," +
                    "title VARCHAR(100) NOT NULL," +
                    "author VARCHAR(50) NOT NULL," +
                    "publish_year INT NOT NULL" +
                    ")";
            stmt.executeUpdate(createBookTable);

            System.out.println("表创建成功");
        }
    }

    private static void handleQuery(Scanner scanner) throws SQLException {
        System.out.println("请选择表：");
        System.out.println("1. 人员表");
        System.out.println("2. 图书表");
        int tableChoice = scanner.nextInt();
        scanner.nextLine();

        try (Connection conn = DriverManager.getConnection(FULL_URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            String tableName = tableChoice == 1 ? "staff" : "books";
            String sql = "SELECT * FROM " + tableName;
            ResultSet rs = stmt.executeQuery(sql);

            if (tableChoice == 1) {
                System.out.println("人员表数据：");
                while (rs.next()) {
                    System.out.printf("卡号：%s, 姓名：%s, 性别：%s, 年龄：%d, 身份证号：%s, 创建时间：%s%n",
                            rs.getString("card_id"),
                            rs.getString("name"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("id_number"),
                            rs.getString("create_time"));
                }
            } else {
                System.out.println("图书表数据：");
                while (rs.next()) {
                    System.out.printf("书本编号：%s, 书名：%s, 作者：%s, 出版年份：%d%n",
                            rs.getString("book_id"),
                            rs.getString("title"),
                            rs.getString("author"),
                            rs.getInt("publish_year"));
                }
            }
        }
    }

    private static void handleInsert(Scanner scanner) throws SQLException {
        System.out.println("请选择表：");
        System.out.println("1. 人员表");
        System.out.println("2. 图书表");
        int tableChoice = scanner.nextInt();
        scanner.nextLine();

        try (Connection conn = DriverManager.getConnection(FULL_URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            if (tableChoice == 1) {
                System.out.println("请输入卡号：");
                String cardId = scanner.nextLine();
                System.out.println("请输入姓名：");
                String name = scanner.nextLine();
                System.out.println("请输入性别（男/女）：");
                String gender = scanner.nextLine();
                System.out.println("请输入年龄：");
                int age = scanner.nextInt();
                scanner.nextLine();
                System.out.println("请输入身份证号：");
                String idNumber = scanner.nextLine();

                String createTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

                String sql = "INSERT INTO staff (card_id, name, gender, age, id_number, create_time) " +
                        "VALUES ('%s', '%s', '%s', %d, '%s', '%s')";
                sql = String.format(sql, cardId, name, gender, age, idNumber, createTime);
                stmt.executeUpdate(sql);
                System.out.println("人员数据插入成功");
            } else {
                System.out.println("请输入书本编号：");
                String bookId = scanner.nextLine();
                System.out.println("请输入书名：");
                String title = scanner.nextLine();
                System.out.println("请输入作者：");
                String author = scanner.nextLine();
                System.out.println("请输入出版年份：");
                int year = scanner.nextInt();
                scanner.nextLine();

                String sql = "INSERT INTO books (book_id, title, author, publish_year) " +
                        "VALUES ('%s', '%s', '%s', %d)";
                sql = String.format(sql, bookId, title, author, year);
                stmt.executeUpdate(sql);
                System.out.println("图书数据插入成功");
            }
        }
    }

    private static void handleDelete(Scanner scanner) throws SQLException {
        System.out.println("请选择表：");
        System.out.println("1. 人员表");
        System.out.println("2. 图书表");
        int tableChoice = scanner.nextInt();
        scanner.nextLine();

        try (Connection conn = DriverManager.getConnection(FULL_URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            if (tableChoice == 1) {
                System.out.println("请输入要删除的人员卡号：");
                String cardId = scanner.nextLine();
                String sql = "DELETE FROM staff WHERE card_id = '" + cardId + "'";
                int rows = stmt.executeUpdate(sql);
                System.out.println(rows > 0 ? "人员数据删除成功" : "未找到该人员");
            } else {
                System.out.println("请输入要删除的书本编号：");
                String bookId = scanner.nextLine();
                String sql = "DELETE FROM books WHERE book_id = '" + bookId + "'";
                int rows = stmt.executeUpdate(sql);
                System.out.println(rows > 0 ? "图书数据删除成功" : "未找到该书");
            }
        }
    }
}